package com.ntanh.mealplanner.main.dao;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import com.ntanh.mealplanner.base.model.BaseEntity;
import com.ntanh.mealplanner.main.objects.DayMeal;
import com.ntanh.mealplanner.main.objects.Ingredient;
import com.ntanh.mealplanner.main.objects.Meal;
import com.ntanh.mealplanner.main.objects.Recipe;
import com.ntanh.mealplanner.main.objects.WeekMeal;

/**
 * Created by Anh Nguyen on 2/27/2015.
 */
public class DataBaseHelper extends SQLiteOpenHelper {

    private static final String DATABASE_NAME = "meal-planner-db";
    private static final int DATABASE_VERSION = 1;

    public static final String RECIPE_TABLE = "recipe";
    public static final String INGREDIENT_TABLE = "ingredient";
    public static final String MEAL_TABLE = "meal";
    public static final String DAY_MEAL_TABLE = "day_meal";
    public static final String WEEK_MEAL_TABLE = "week_meal";

    public static final String CREATE_INGREDIENT_TABLE = "CREATE TABLE "
            + INGREDIENT_TABLE + " ("
            + Ingredient.ID + " INTEGER PRIMARY KEY, "
            + Ingredient.CREATEDDATE + " DATETIME, "
            + Ingredient.MODIFIEDDATE + " DATETIME, "
            + Ingredient.CREATEDBY + " TEXT, "
            + Ingredient.MODIFIEDBY + " TEXT, "
            + Ingredient.ACTIVE + " INTEGER, "
            + Ingredient.DELETED + " INTEGER, "
            + Ingredient.SYNCED + " INTEGER, "
            + Ingredient.NAME + " TEXT, "
            + Ingredient.IMAGE + " TEXT, "
            + Ingredient.DESCRIPTION + " TEXT, "
            + Ingredient.UNIT + " TEXT"
            + ")";

    public static final String CREATE_RECIPE_TABLE = "CREATE TABLE "
            + RECIPE_TABLE + " ("
            + Recipe.ID + " INTEGER PRIMARY KEY, "
            + Recipe.CREATEDDATE + " DATETIME, "
            + Recipe.MODIFIEDDATE + " DATETIME, "
            + Recipe.CREATEDBY + " TEXT, "
            + Recipe.MODIFIEDBY + " TEXT, "
            + Recipe.ACTIVE + " INTEGER, "
            + Recipe.DELETED + " INTEGER, "
            + Recipe.SYNCED + " INTEGER, "
            + Recipe.NAME + " TEXT, "
            + Recipe.IMAGE + " TEXT, "
            + Recipe.DESCRIPTION + " TEXT, "
            + Recipe.INGREDIENT_LIST + " TEXT, "
            + Recipe.SOURCE_URL + " TEXT"
            + ")";

    public static final String CREATE_MEAL_TABLE = "CREATE TABLE "
            + MEAL_TABLE + " ("
            + Meal.ID + " INTEGER PRIMARY KEY, "
            + Meal.CREATEDDATE + " DATETIME, "
            + Meal.MODIFIEDDATE + " DATETIME, "
            + Meal.CREATEDBY + " TEXT, "
            + Meal.MODIFIEDBY + " TEXT, "
            + Meal.ACTIVE + " INTEGER, "
            + Meal.DELETED + " INTEGER, "
            + Meal.SYNCED + " INTEGER, "
            + Meal.RECIPE_LIST + " TEXT, "
            + Meal.MEAL_TYPE + " TEXT"
            + ")";

    public static final String CREATE_DAY_MEAL_TABLE = "CREATE TABLE "
            + DAY_MEAL_TABLE + " ("
            + DayMeal.ID + " INTEGER PRIMARY KEY, "
            + DayMeal.CREATEDDATE + " DATETIME, "
            + DayMeal.MODIFIEDDATE + " DATETIME, "
            + DayMeal.CREATEDBY + " TEXT, "
            + DayMeal.MODIFIEDBY + " TEXT, "
            + DayMeal.ACTIVE + " INTEGER, "
            + DayMeal.DELETED + " INTEGER, "
            + DayMeal.SYNCED + " INTEGER, "
            + DayMeal.MEAL_LIST + " TEXT, "
            + DayMeal.DATE + " DATETIME"
            + ")";

    public static final String CREATE_WEEK_MEAL_TABLE = "CREATE TABLE "
            + WEEK_MEAL_TABLE + " ("
            + WeekMeal.ID + " INTEGER  PRIMARY KEY, "
            + WeekMeal.CREATEDDATE + " DATETIME, "
            + WeekMeal.MODIFIEDDATE + " DATETIME, "
            + WeekMeal.CREATEDBY + " TEXT, "
            + WeekMeal.MODIFIEDBY + " TEXT, "
            + WeekMeal.ACTIVE + " INTEGER, "
            + WeekMeal.DELETED + " INTEGER, "
            + WeekMeal.SYNCED + " INTEGER, "
            + WeekMeal.DAY_MEAL_LIST + " TEXT, "
            + WeekMeal.START_DATE + " DATETIME"
            + ")";

    private static DataBaseHelper instance;

    public static synchronized DataBaseHelper getHelper(Context context) {
        if (instance == null)
            instance = new DataBaseHelper(context);
        return instance;
    }

    private DataBaseHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onOpen(SQLiteDatabase db) {
        super.onOpen(db);
        if (!db.isReadOnly()) {
            // Enable foreign key constraints
            db.execSQL("PRAGMA foreign_keys=ON;");
        }
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(CREATE_INGREDIENT_TABLE);
        db.execSQL(CREATE_RECIPE_TABLE);
        db.execSQL(CREATE_MEAL_TABLE);
        db.execSQL(CREATE_DAY_MEAL_TABLE);
        db.execSQL(CREATE_WEEK_MEAL_TABLE);
    }

    private static final String SQL_DELETE_ENTRIES = "DROP TABLE IF EXISTS ";

    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // This database is only a cache for online data, so its upgrade policy is
        // to simply to discard the data and start over
        db.execSQL(SQL_DELETE_ENTRIES + INGREDIENT_TABLE);
        db.execSQL(SQL_DELETE_ENTRIES + RECIPE_TABLE);
        db.execSQL(SQL_DELETE_ENTRIES + MEAL_TABLE);
        db.execSQL(SQL_DELETE_ENTRIES + DAY_MEAL_TABLE);
        db.execSQL(SQL_DELETE_ENTRIES + WEEK_MEAL_TABLE);
        onCreate(db);
    }
    public void onDowngrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        onUpgrade(db, oldVersion, newVersion);
    }

}
